"""
    Author:
    Date: 2018-10-26

    用户管理类
"""

import json
import datetime
import time

from apps.gma import helpers as gma_helpers
from django.conf import settings
from entities import photo_mgr
from entities.consts import *
from utils import db, util
from utils.log import Log

g_mch_bill_no = 1


def get_assess_info(channel, day):
    # 新增用户数量
    new_user_count = get_new_user_count_of_channel(channel, day)
    # 新增付费用户数量
    new_pay_user_count = get_new_pay_user_count_of_channel(channel, day)
    # 老的付费用户数量
    old_pay_user_count = get_old_pay_user_count_of_channel(channel, day)
    # 新增用户充值数量
    pay_amount = get_total_pay_amount_of_channel(channel, day)
    # 新增用户充值数量
    new_pay_amount = get_new_user_total_pay_amount_of_channel(channel, day)
    # 老用户充值数量
    old_pay_amount = get_old_user_total_pay_amount_of_channel(channel, day)
    # 复充的用户数量
    repay_user_count = get_repay_user_count_of_channel(channel, day)

    info = {
        'new_user_count': new_user_count,
        'new_pay_user_count': new_pay_user_count,
        'old_pay_user_count': old_pay_user_count,
        'pay_amount': pay_amount,
        'new_pay_amount': new_pay_amount,
        'old_pay_amount': old_pay_amount,
        'repay_user_count': repay_user_count,
        'PUR': (100.0*new_pay_user_count/new_user_count) if new_user_count > 0 else 0,  # 付费渗透率
        'RPUR': (100.0*repay_user_count/new_pay_user_count) if new_pay_user_count > 0 else 0,  # 复充率
        'ARPU': (new_pay_amount/new_user_count) if new_user_count > 0 else 0,
        'ARPPU': (new_pay_amount/new_pay_user_count) if new_pay_user_count > 0 else 0,
    }
    return info


def get_user_detail(uid):
    """ 获取用户详情 """
    if is_broadcaster(uid):
        return get_broadcaster_detail(uid)
    else:
        return get_watcher_detail(uid)


def fetch_mch_billno(uid):
    """ 商户订单号（每个订单号必须唯一。取值范围：0~9，a~z，A~Z） """
    global g_mch_bill_no

    timestamp = int(time.time())
    g_mch_bill_no += 1
    billno = f'{timestamp}u{uid}i{g_mch_bill_no}'
    return billno


def user_apply_for_withdraw(openid, uid, cost_diamond, withdraw_amount):
    """ 代理提现 """
    if withdraw_amount <= 0:
        Log.error(f'Invalid withdraw amount: {withdraw_amount}')
        return False

    user = get_user_detail(uid)
    if user is None:
        Log.error(f'Not find user: {uid}')
        return False

    if user['diamond'] < cost_diamond:
        Log.error(f'Lack diamond: uid={uid}, diamond={cost_diamond}, withdraw={withdraw_amount}')
        return False

    mch_billno = fetch_mch_billno(uid)
    success = db_user_apply_for_withdraw(openid, uid, cost_diamond, withdraw_amount, mch_billno)
    if not success:
        Log.error(f'User apply for withdraw failed: uid={uid}, amount={withdraw_amount}')
        return False

    Log.info(f'user apply for withdraw ok: uid={uid}, amount={withdraw_amount}')
    return True


def get_user_red_pack_logs(uid):
    """ 提取用户的领取红包日志 """
    logs = db_get_user_withdraw_logs(uid, 100)
    for log in logs:
        log['red_pack_status_desp'] = get_red_pack_status_desp(log['red_pack_status'])
        log['auth_status_desp'] = get_auth_status_desp(log['auth_status'])
    return logs


def get_user_origin_bundle_id(uid):
    """ 用户用户的起始bundle_id """
    user = get_user_detail(uid)
    return user['origin_bundle_id'] if user else ''


def is_valid_uid(uid):
    """ 判断是否是有效的uid """
    if not str(uid).isdigit():
        return False

    sql = f'''
        SELECT      id
        FROM        cmf_users
        WHERE       id={uid}
        LIMIT       1;
    '''
    data = db.query_data_from_db(sql)
    return len(data) == 1


def is_valid_token(uid, token):
    """ 判断是否是有效的token """
    sql = f'''
        SELECT      id
        FROM        cmf_users
        WHERE       id=%(uid)s AND
                    token=%(token)s
        LIMIT       1;
    '''
    params = {
        'uid': str(uid),
        'token': token,
    }
    data = db.query_data_from_db(sql, params)
    return len(data) == 1


def get_watcher_detail(uid):
    """ 获取用户的详情 """
    sql = f'''
        SELECT      *,
                    u.id AS uid
        FROM        cmf_users AS u
        WHERE       u.id={uid}
        LIMIT       1;
    '''
    data = db.query_data_from_db(sql)
    return data[0] if data and len(data) == 1 else None


def get_user_detail_by_mobile(mobile):
    """ 获取用户的详情 """
    sql = f'''
        SELECT      *,
                    u.id AS uid
        FROM        cmf_users AS u
        WHERE       u.mobile=%(mobile)s
        LIMIT       1;
    '''
    params = {
        'mobile': mobile,
    }
    data = db.query_data_from_db(sql, params)
    return data[0] if data else None


def get_user_auth_detail(uid):
    """ 获取用户的认证详情 """
    sql = f'''
        SELECT      *
        FROM        cmf_users_auth AS u
        WHERE       u.id={uid}
        LIMIT       1;
    '''
    data = db.query_data_from_db(sql)
    return data[0] if data and len(data) == 1 else None


def get_broadcaster_detail(uid):
    """ 获取主播的详情 """
    sql = f'''
        SELECT      *,
                    u.id AS uid,
                    u.nickname AS auth_nickname,
                    u.city AS auth_city,
                    u.signature AS auth_signature,
                    a.id_number,
                    a.id_upside_image,
                    a.id_downside_image,
                    a.id_with_user_image
        FROM        cmf_users AS u
        LEFT JOIN   cmf_users_auth as a ON a.uid=u.id
        LEFT JOIN   cmf_config_room_tags AS t ON t.tag_id=a.room_tag
        WHERE       u.id={uid}
        LIMIT       1;
    '''
    data = db.query_data_from_db(sql)
    if data is None or not data:
        return None

    user = data[0]
    show_pictures = photo_mgr.get_user_show_pictures(uid)
    user['images'] = show_pictures
    user['uid'] = uid
    if not user['room_id']:
        user['room_id'] = user['id']

    return user


def get_user_detail_by_unionid(unionid):
    """ 根据用户的微信unionid获取用户的详情 """
    sql = f'''
        SELECT      *
        FROM        cmf_users AS u
        WHERE       u.unionid="{unionid}"
        LIMIT       1;
    '''
    data = db.query_data_from_db(sql)
    return data[0] if data and len(data) == 1 else []


# def get_user_detail_by_mobile(mobile):
#     """ 根据用户的手机号码获取用户的详情 """
#     sql = '''
#         SELECT      *
#         FROM        cmf_users AS u
#         WHERE       u.mobile=%(mobile)s
#         LIMIT       1;
#     '''
#     params = {
#         'mobile': mobile,
#     }
#     data = db.query_data_from_db(sql, params)
#     return data[0] if data and len(data) == 1 else []


def is_broadcaster(uid):
    """ 判断用户是否是主播 """
    sql = f'''
        SELECT      COUNT(*) AS count
        FROM        cmf_users_auth AS a
        WHERE       a.uid={uid};
    '''
    data = db.query_data_from_db(sql)
    if data is None:
        return False
    return data[0]['count'] == 1


def is_authorised_broadcaster(uid):
    """ 判断用户是否是认证过的主播 """
    sql = f'''
        SELECT      COUNT(*) AS count
        FROM        cmf_users_auth AS a
        JOIN        cmf_users AS u ON a.uid=u.id
        WHERE       u.id={uid} AND a.status={AUTH_STATUS_CERTIFIED};
    '''
    data = db.query_data_from_db(sql)
    if data is None:
        return False
    return data[0]['count'] == 1


# def get_waiting_auth_user_list(count):
#     """ 获取等待认证的用户列表 """
#     sql = f'''
#         SELECT      *
#         FROM        cmf_users_auth AS a
#         JOIN        cmf_users AS u ON a.uid=u.id
#         WHERE       a.status={AUTH_STATUS_WAITING}
#         ORDER BY    a.id DESC
#         LIMIT       {count};
#     '''
#     data = db.query_data_from_db(sql)
#     return [] if data is None else data


def get_auth_user_list_by_status(status, count=20, request=None):
    """ 获取等待认证的用户列表 """
    apps_condition = gma_helpers.build_app_filter_condition_for_request(request)
    sql = f'''
        SELECT      *
        FROM        cmf_users_auth AS a
        JOIN        cmf_users AS u ON a.uid=u.id
        WHERE       {apps_condition} AND
                    a.status={status}
        ORDER BY    a.update_time DESC
        LIMIT       {count};
    '''
    data = db.query_data_from_db(sql)
    if data is None:
        return []

    for record in data:
        # record['avatar'] = util.cdn_url(record['avatar'])
        # record['star_desp'] = '⭐️' * record['star']
        record['is_ios_device'] = 'iphone' in record['last_login_phone'].lower()

    return data


def get_user_list_by_star(star, count=20, request=None):
    """ 获取指定星级的用户列表 """
    apps_condition = gma_helpers.build_app_filter_condition_for_request(request)
    sql = f'''
        SELECT      *
        FROM        cmf_users_auth AS a
        JOIN        cmf_users AS u ON a.uid=u.id
        WHERE       {apps_condition} AND
                    a.star={star} AND
                    a.status={AUTH_STATUS_CERTIFIED}
        ORDER BY    a.id DESC
        LIMIT       {count};
    '''
    data = db.query_data_from_db(sql)
    if data is None:
        return []

    for record in data:
        record['avatar'] = util.cdn_url(record['avatar'])
        record['star_desp'] = '⭐️' * record['star']
        record['is_ios_device'] = 'iphone' in record['last_login_phone'].lower()

    return data


def get_waiting_auth_user_count_by_status(status, request=None):
    """ 获取等待认证的用户数量 """
    apps_condition = gma_helpers.build_app_filter_condition_for_request(request)

    sql = f'''
        SELECT      COUNT(*) AS count
        FROM        cmf_users_auth AS a
        JOIN        cmf_users AS u on u.id=a.uid
        WHERE       {apps_condition} AND
                    a.status={status};
    '''
    data = db.query_data_from_db(sql)
    if data is None:
        return 0
    return data[0]['count']


def update_broadcaster_auth_status(uid, status):
    """ 更新主播的认证状态 """
    now = util.now()
    sql = f'''
        UPDATE      cmf_users_auth
        SET         status={status},
                    update_time="{now}"
        WHERE       uid={uid}
        LIMIT       1;
    '''
    affected_rows = db.execute_sql(sql)
    return affected_rows == 1


def get_broadcaster_list(count=50, request=None):
    """ 获取主播列表 """
    gm_id = gma_helpers.get_request_gm_id(request)
    franchise_bundle_id = 'com.yinlong.xiangyu' if gm_id in [24, 36, 37] else None

    if franchise_bundle_id is not None:
        sql = f'''
            SELECT      *
            FROM        cmf_users_auth AS a
            JOIN        cmf_users AS u ON a.uid=u.id
            WHERE       a.status={AUTH_STATUS_CERTIFIED} AND
                        u.bundle_id='{franchise_bundle_id}'
            ORDER BY    u.is_busy,
                        a.recommend_value DESC,
                        a.activity DESC,
                        a.star DESC,
                        a.id DESC
            LIMIT       {count};
        '''
    else:
        sql = f'''
            SELECT      *
            FROM        cmf_users_auth AS a
            JOIN        cmf_users AS u ON a.uid=u.id
            WHERE       a.status={AUTH_STATUS_CERTIFIED}
            ORDER BY    u.is_busy,
                        CASE u.bundle_id
                            WHEN '{franchise_bundle_id}' THEN 1
                            ELSE 0
                        END,
                        a.recommend_value DESC,
                        a.activity DESC,
                        a.star DESC,
                        a.id DESC
            LIMIT       {count};
        '''

    data = db.query_data_from_db(sql)
    if data is None:
        return []

    for record in data:
        record['avatar'] = util.cdn_url(record['avatar'])
        record['star_desp'] = '⭐️' * record['star']
        record['is_ios_device'] = 'iphone' in record['last_login_phone'].lower()
    return data


def get_recommend_user_count(request=None):
    """ 获取推荐主播的列表 """
    apps_condition = gma_helpers.build_app_filter_condition_for_request(request)
    sql = f'''
        SELECT      COUNT(*) AS count
        FROM        cmf_users_auth AS a
        JOIN        cmf_users AS u ON u.id=a.uid
        WHERE       {apps_condition} AND
                    a.recommend_value > 0;
    '''
    data = db.query_data_from_db(sql)
    return data[0]['count'] if data else 0


def get_certified_broadcaster_count():
    """ 获取认证通过的主播数量 """
    sql = f'''
        SELECT      COUNT(*) AS count
        FROM        cmf_users_auth AS a
        WHERE       a.status={AUTH_STATUS_CERTIFIED};
    '''
    data = db.query_data_from_db(sql)
    if data is None:
        return 0
    return data[0]['count']


def get_watcher_list(count, request=None):
    """ 获取观众列表 """
    apps_condition = gma_helpers.build_app_filter_condition_for_request(request)
    sql = f'''
        SELECT      u.*, u.id AS uid
        FROM        cmf_users AS u
        LEFT JOIN   cmf_users_auth AS a ON a.uid=u.id
        WHERE       {apps_condition} AND
                    a.status IS NULL
        ORDER BY    a.id DESC
        LIMIT       {count};
    '''
    data = db.query_data_from_db(sql)
    if data is None:
        return []

    for record in data:
        record['avatar'] = util.cdn_url(record['avatar'])
        record['star_desp'] = ''
        record['recommend_value'] = 0
        record['is_ios_device'] = 'iphone' in record['last_login_phone'].lower()

    return data


def get_vip_list(count, request=None):
    """ 获取VIP用户列表 """
    apps_condition = gma_helpers.build_app_filter_condition_for_request(request)
    sql = f'''
        SELECT      u.*,
                    u.id AS uid,
                    v.card_type AS card_type,
                    v.add_time AS buy_vip_card_time
        FROM        cmf_users_vip AS v
        LEFT JOIN   cmf_users AS u ON v.uid=u.id
        WHERE       {apps_condition} AND
                    u.is_test_account=0
        ORDER BY    v.id DESC
        LIMIT       {count};
    '''
    data = db.query_data_from_db(sql)
    if data is None:
        return []

    for record in data:
        record['avatar'] = util.cdn_url(record['avatar'])
        record['star_desp'] = ''
        record['is_ios_device'] = 'iphone' in record['last_login_phone'].lower()
    return data


def get_total_watcher_count(request=None):
    """ 获取观众的数量 """
    apps_condition = gma_helpers.build_app_filter_condition_for_request(request)
    sql = f'''
        SELECT      COUNT(*) AS count
        FROM        cmf_users AS u
        LEFT JOIN   cmf_users_auth AS a ON a.uid=u.id
        WHERE       {apps_condition} AND
                    (a.status!={AUTH_STATUS_CERTIFIED} OR a.status IS NULL);
    '''
    data = db.query_data_from_db(sql)
    if data is None:
        return 0
    return data[0]['count']


def get_total_vip_count(request=None):
    """ 获取VIP用户的数量 """
    apps_condition = gma_helpers.build_app_filter_condition_for_request(request)
    sql = f'''
        SELECT      COUNT(DISTINCT(v.uid)) AS count
        FROM        cmf_users_vip AS v
        JOIN        cmf_users AS u ON u.id=v.uid
        WHERE       {apps_condition} AND
                    u.is_test_account=0;
    '''
    data = db.query_data_from_db(sql)
    return data[0]['count'] if data else 0


def search_user(search_value, count=100, request=None):
    """ 获取用户列表 """
    apps_condition = gma_helpers.build_app_filter_condition_for_request(request)

    sql = f'''
        SELECT      *
        FROM        cmf_users AS u
        WHERE       {apps_condition} AND
                    (u.nickname LIKE "%{ search_value }%" OR
                    u.last_login_ip LIKE "%{ search_value }%" OR
                    u.last_login_device LIKE "%{ search_value }%")
        ORDER BY    u.id DESC
        LIMIT       {count};
    '''
    data = db.query_data_from_db(sql)
    return [] if data is None else data


def search_watcher(search_value, count):
    """ 获取用户列表 """
    sql = f'''
        SELECT      u.*, a.*
        FROM        cmf_users AS u
        LEFT JOIN   cmf_users_auth AS a ON a.uid=u.id
        WHERE       (a.status!={AUTH_STATUS_CERTIFIED} OR a.status IS NULL)
                    AND u.nickname LIKE "%{ search_value }%"
        ORDER BY    a.status DESC, a.id DESC
        LIMIT       {count};
    '''
    data = db.query_data_from_db(sql)
    return [] if data is None else data


def search_broadcaster(search_value, count):
    """ 获取主播列表 """
    sql = f'''
        SELECT      u.*, a.*
        FROM        cmf_users_auth AS a
        LEFT JOIN   cmf_users AS u ON a.uid=u.id
        WHERE       a.status={AUTH_STATUS_CERTIFIED}
                    AND u.nickname LIKE "%{ search_value }%"
        ORDER BY    a.status DESC, a.id DESC
        LIMIT       {count};
    '''
    data = db.query_data_from_db(sql)
    return [] if data is None else data


def get_uid_from_unionid(unionid):
    """ 根据openid查找用户id """
    sql = f'''
        SELECT      *
        FROM        cmf_users AS u
        WHERE       u.unionid="{unionid}"
        LIMIT       1;
    '''
    data = db.query_data_from_db(sql)
    if not data:
        return None

    user = data[0]
    return user['id']


def get_uid_from_account(account):
    """ 根据account查找用户id """
    sql = f'''
        SELECT      *
        FROM        cmf_users AS u
        WHERE       u.account="{account}"
        LIMIT       1;
    '''
    data = db.query_data_from_db(sql)
    if not data:
        return None

    user = data[0]
    return user['id']


def get_uid_from_mobile(mobile):
    """ 根据mobile查找用户id """
    sql = f'''
        SELECT      *
        FROM        cmf_users AS u
        WHERE       u.mobile="{mobile}"
        LIMIT       1;
    '''
    data = db.query_data_from_db(sql)
    return data[0]['id'] if data else None


def get_user_recharge_list(uid, count):
    """ 获取用户的充值列表 """
    sql = f'''
        SELECT      c.*
        FROM        cmf_users_charge    AS c
        WHERE       c.uid={uid}         AND
                    c.status=1
        ORDER BY    c.id DESC
        LIMIT       {count};
    '''
    data = db.query_data_from_db(sql)
    return [] if data is None else data


def get_user_total_paid_amount(uid):
    """ 获取用户的累计充值数 """
    sql = f'''
        SELECT      IFNULL(SUM(money), 0)    AS total_money
        FROM        cmf_users_charge        AS c
        WHERE       c.uid={uid}             AND
                    c.status=1;
    '''
    data = db.query_data_from_db(sql)
    return data[0]['total_money'] if data else 0


def get_user_total_paid_diamond_amount(uid):
    """ 获取用户的累计充值钻石数 """
    sql = f'''
        SELECT      IFNULL(SUM(coin), 0)    AS total_diamond
        FROM        cmf_users_charge        AS c
        WHERE       c.uid={uid}             AND
                    c.status=1;
    '''
    data = db.query_data_from_db(sql)
    return data[0]['total_diamond'] if data else 0


def get_user_total_paid_money_amount(uid):
    """ 获取用户的累计充值金额 """
    sql = f'''
        SELECT      IFNULL(SUM(money), 0)    AS total_diamond
        FROM        cmf_users_charge        AS c
        WHERE       c.uid={uid}             AND
                    c.status=1;
    '''
    data = db.query_data_from_db(sql)
    return data[0]['total_diamond'] if data else 0


def get_user_withdraw_count_today(uid):
    """ 获取用户今日的提现次数 """
    start_time = util.timetostr(util.get_today_timestamp())
    sql = f'''
        SELECT      COUNT(*) AS withdraw_count
        FROM        cmf_users_withdraw
        WHERE       uid={uid} AND
                    create_time>="{start_time}";
    '''
    data = db.query_data_from_db(sql)
    return data[0]['withdraw_count'] if data else 0


def db_user_apply_for_withdraw(openid, uid, cost_diamond, withdraw_amount, mch_billno):
    """ 用户申请提现.

        * 减少cmf_users中的money值
        * 增加添加提现记录, 等待审核
    """
    sql = f'''
        UPDATE      cmf_users
        SET         diamond=diamond-%(cost_diamond)s
        WHERE       id=%(uid)s AND
                    diamond>=%(cost_diamond)s;

        INSERT INTO cmf_users_withdraw
        SET         uid=%(uid)s,
                    openid=%(openid)s,
                    withdraw_amount=%(withdraw_amount)s,
                    mch_billno=%(mch_billno)s,
                    auth_status={AUTH_STATUS_WAITING},
                    comment='';
    '''
    params = {
        'uid': str(uid),
        'openid': openid,
        'cost_diamond': cost_diamond,
        'withdraw_amount': withdraw_amount,
        'mch_billno': mch_billno,
    }
    affected_rows = db.execute_sql(sql, params)
    return affected_rows == 1


def db_get_user_withdraw_logs(uid, count):
    """ 获取指定用户的提现记录 """
    sql = f'''
        SELECT      *
        FROM        cmf_users_withdraw
        WHERE       uid={uid}
        ORDER BY    id DESC
        LIMIT       {count};
    '''
    data = db.query_data_from_db(sql)
    return data if data else []


def get_user_red_pack_count(uid):
    """ 获取用户的红包个数  """
    sql = f'''
        SELECT      COUNT(*) AS total_count
        FROM        cmf_users_withdraw AS w
        WHERE       w.uid={uid};
    '''
    data = db.query_data_from_db(sql)
    return data[0]['total_count'] if data else 0


def get_authing_withdraw_count():
    """ 获取用户的提现审核记录数量 """
    sql = f'''
        SELECT      COUNT(*) AS withdraw_count
        FROM        cmf_users_withdraw AS w
        WHERE       w.auth_status={AUTH_STATUS_WAITING};
    '''
    data = db.query_data_from_db(sql)
    return data[0]['withdraw_count'] if data else 0


def get_authing_user_withdraw_list(count):
    """ 获取用户的提现审核记录 """
    sql = f'''
        SELECT      w.*,
                    u.nickname,
                    u.avatar
        FROM        cmf_users_withdraw AS w
        JOIN        cmf_users AS u ON w.uid=u.id
        WHERE       w.auth_status={AUTH_STATUS_WAITING}
        ORDER BY    id DESC
        LIMIT       {count};
    '''
    data = db.query_data_from_db(sql)
    return data if data else []


def get_user_withdraw_info(wid):
    """ 获取用户的提现记录 """
    sql = f'''
        SELECT      *
        FROM        cmf_users_withdraw
        WHERE       id={wid}
        LIMIT       1;
    '''
    data = db.query_data_from_db(sql)
    return data[0] if data and len(data) == 1 else None


def update_user_withdraw_to_agree_status(wid):
    """ 更新用户的提现状态为同意状态 """
    now = util.now()
    sql = f'''
        UPDATE      cmf_users_withdraw
        SET         auth_status={AUTH_STATUS_CERTIFIED},
                    red_pack_status={RED_PACK_STATUS_SENDING},
                    update_time="{now}"
        WHERE       id={wid} AND auth_status={AUTH_STATUS_WAITING}
        LIMIT       1;
    '''
    affected_rows = db.execute_sql(sql)
    return affected_rows == 1


def update_user_red_pack_status(wid, status):
    """ 更新用户的红包状态 """
    now = util.now()
    sql = f'''
        UPDATE      cmf_users_withdraw
        SET         red_pack_status={status},
                    update_time="{now}"
        WHERE       id={wid}
        LIMIT       1;
    '''
    affected_rows = db.execute_sql(sql)
    return affected_rows == 1


def update_user_withdraw_comment(wid, comment):
    """ 更新用户提现备注 """
    sql = '''
        UPDATE      cmf_users_withdraw
        SET         comment=%(comment)s
        WHERE       id=%(wid)s
        LIMIT       1;
    '''
    params = {
        'wid': str(wid),
        'comment': comment,
    }
    affected_rows = db.execute_sql(sql, params)
    return affected_rows == 1


def update_user_withdraw_to_reject_status(wid):
    """ 更新用户的提现审核状态 """
    now = util.now()
    sql = f'''
        UPDATE      cmf_users_withdraw
        SET         auth_status={AUTH_STATUS_REJECTED},
                    update_time="{now}"
        WHERE       id={wid}
        LIMIT       1;
    '''
    affected_rows = db.execute_sql(sql)
    return affected_rows == 1


def get_rejected_user_withdraw_list(count):
    """ 获取已拒绝的用户提现记录列表 """
    sql = f'''
        SELECT      w.*,
                    u.nickname,
                    u.avatar
        FROM        cmf_users_withdraw AS w
        JOIN        cmf_users AS u ON w.uid=u.id
        WHERE       w.auth_status={AUTH_STATUS_REJECTED}
        ORDER BY    id DESC
        LIMIT       {count};
    '''
    data = db.query_data_from_db(sql)
    return data if data else []


def get_agreed_user_withdraw_list(count):
    """ 获取已同意的用户提现记录列表 """
    sql = f'''
        SELECT      w.*,
                    u.nickname,
                    u.avatar
        FROM        cmf_users_withdraw AS w
        JOIN        cmf_users AS u ON w.uid=u.id
        WHERE       w.auth_status={AUTH_STATUS_CERTIFIED}
        ORDER BY    id DESC
        LIMIT       {count};
    '''
    data = db.query_data_from_db(sql)
    return data if data else []


def get_rejected_user_withdraw_count():
    """ 获取被拒的用户提现记录数量 """
    sql = f'''
        SELECT      IFNULL(COUNT(*), 0) AS withdraw_count
        FROM        cmf_users_withdraw AS w
        WHERE       w.auth_status={AUTH_STATUS_REJECTED};
    '''
    data = db.query_data_from_db(sql)
    return data[0]['withdraw_count'] if data else 0


def get_agreed_user_withdraw_count():
    """ 获取同意的用户提现记录数量 """
    sql = f'''
        SELECT      IFNULL(COUNT(*), 0) AS withdraw_count
        FROM        cmf_users_withdraw AS w
        WHERE       w.auth_status={AUTH_STATUS_CERTIFIED};
    '''
    data = db.query_data_from_db(sql)
    return data[0]['withdraw_count'] if data else 0


def create_wx_login_user(openid, unionid, nickname, avatar):
    """ 创建使用微信登录的用户 """
    sql = '''
        INSERT INTO cmf_users
        SET         account=%(openid)s,
                    openid=%(openid)s,
                    unionid=%(unionid)s,
                    nickname=%(nickname)s,
                    avatar=%(avatar)s,
                    country="",
                    wx_unionid="",
                    last_login_phone="",
                    last_login_version="",
                    bundle_id="",
                    origin_channel="",
                    origin_bundle_id="";
    '''
    params = {
        'openid': openid,
        'unionid': unionid,
        'nickname': nickname,
        'avatar': avatar,
    }
    affected_rows = db.execute_sql(sql, params)
    return affected_rows == 1


def create_mobile_login_user(mobile, nickname, avatar):
    """ 创建使用手机登录的用户 """
    sql = '''
        INSERT INTO cmf_users
        SET         mobile=%(mobile)s,
                    nickname=%(nickname)s,
                    avatar=%(avatar)s,
                    last_login_phone="",
                    last_login_version="",
                    bundle_id="com.haibao.gma.test",
                    origin_bundle_id="com.haibao.gma.test";
    '''
    params = {
        'mobile': mobile,
        'nickname': nickname,
        'avatar': avatar,
    }
    affected_rows = db.execute_sql(sql, params)
    return affected_rows == 1


def get_total_user_count():
    """ 获取用户总数 """
    sql = '''
        SELECT      COUNT(*) AS count
        FROM        cmf_users;
    '''
    data = db.query_data_from_db(sql)
    return 0 if data is None else data[0]['count']


def get_total_premium_user_count():
    """ 获取充值用户的数量 """
    sql = '''
        SELECT      COUNT(DISTINCT(uid)) AS total_count
        FROM        cmf_users_charge
        WHERE       status=1;
    '''
    data = db.query_data_from_db(sql)
    return data[0]['total_count'] if data else 0


def get_total_test_user_count():
    """ 获取测试用户总数 """
    sql = '''
        SELECT      COUNT(*) AS count
        FROM        cmf_users
        WHERE       is_test_account=1;
    '''
    data = db.query_data_from_db(sql)
    return data[0]['count'] if data else 0


def get_all_premium_users():
    """ 获取所有的充值用户的id """
    sql = '''
        SELECT      DISTINCT(uid) AS uid
        FROM        cmf_users_charge
        WHERE       status=1;
    '''
    data = db.query_data_from_db(sql)
    return data if data else []


def get_total_broadcaster_count(request=None):
    """ 获取主播的数量 """
    apps_condition = gma_helpers.build_app_filter_condition_for_request(request)
    sql = f'''
        SELECT      COUNT(*) AS count
        FROM        cmf_users_auth AS a
        JOIN        cmf_users AS u ON u.id=a.uid
        WHERE       {apps_condition} AND
                    a.status={AUTH_STATUS_CERTIFIED};
    '''
    data = db.query_data_from_db(sql)
    return 0 if data is None else data[0]['count']


def get_all_casters():
    """ 获取所有的主播 """
    sql = f'''
        SELECT      uid
        FROM        cmf_users_auth
        WHERE       status={AUTH_STATUS_CERTIFIED};
    '''
    data = db.query_data_from_db(sql)
    return data if data else []


def get_all_test_users():
    """ 获取所有的测试用户 """
    sql = f'''
        SELECT      id AS uid
        FROM        cmf_users
        WHERE       is_test_account=1;
    '''
    data = db.query_data_from_db(sql)
    return data if data else []


def get_total_online_broadcaster_count():
    """ 获取在线主播的数量 """
    sql = f'''
        SELECT      COUNT(*) AS count
        FROM        cmf_users_auth AS a
        JOIN        cmf_users AS u ON u.id=a.uid
        WHERE       a.status={AUTH_STATUS_CERTIFIED} AND
                    u.is_busy=0;
    '''
    data = db.query_data_from_db(sql)
    return 0 if data is None else data[0]['count']


def get_all_online_casters():
    """ 获取所有的在线主播 """
    sql = f'''
        SELECT      a.uid
        FROM        cmf_users_auth AS a
        JOIN        cmf_users AS u ON u.id=a.uid
        WHERE       a.status={AUTH_STATUS_CERTIFIED} AND
                    u.is_busy=0;
    '''
    data = db.query_data_from_db(sql)
    return data if data else []


def get_total_diamond_amount():
    """ 获取剩余钻石总量 """
    sql = '''
        SELECT      SUM(diamond) AS count
        FROM        cmf_users;
    '''
    data = db.query_data_from_db(sql)
    return 0 if data is None else data[0]['count']


def get_total_ticket_amount():
    """ 获取剩余聊天总量 """
    sql = '''
        SELECT      SUM(ticket) AS count
        FROM        cmf_users;
    '''
    data = db.query_data_from_db(sql)
    return 0 if data is None else data[0]['count']


def get_broadcaster_total_call_seconds(uid):
    """ 获取主播的累计通话时长 """
    sql = f'''
        SELECT      IFNULL(SUM(chat_seconds),0) AS seconds
        FROM        cmf_vchat_call_log
        WHERE       caller_uid={uid} OR callee_uid={uid};
    '''
    data = db.query_data_from_db(sql)
    return 0 if data is None else data[0]['seconds']


def get_broadcaster_total_online_seconds(uid):
    """ 获取主播的累计在线时长 """
    settled_seconds = get_broadcaster_total_settled_online_seconds(uid)
    unsettled_seconds = get_broadcaster_total_unsettled_online_seconds(uid)
    return settled_seconds + unsettled_seconds


def get_broadcaster_total_settled_online_seconds(uid):
    """ 获取主播的累计在线时长 """
    sql = f'''
        SELECT      IFNULL(SUM(online_seconds),0) AS seconds
        FROM        cmf_users_online_log
        WHERE       uid={uid};
    '''
    data = db.query_data_from_db(sql)
    return 0 if data is None else data[0]['seconds']


def get_broadcaster_total_unsettled_online_seconds(uid):
    """ 获取主播未结算的在线时间 """
    unsettled_seconds = 0
    user = get_user_detail(uid)
    if user is not None and user['is_busy'] == 0:
        unsettled_seconds = min(24*3600, int(time.time() - util.datetimetotime(user['set_busy_time'])))
    return unsettled_seconds


def get_broadcaster_today_online_seconds(uid):
    """ 获取主播的今日的在线时长 """
    begin_time = util.datetimetostr(datetime.date.today())
    end_time = util.datetimetostr(datetime.date.today() + datetime.timedelta(days=1))
    settled_seconds = get_broadcaster_online_seconds(uid, begin_time, end_time)

    unsettled_seconds = get_broadcaster_total_unsettled_online_seconds(uid)
    return settled_seconds + unsettled_seconds


def get_broadcaster_online_records(uid, begin_time, end_time):
    """ 获取主播的在线时长 """
    sql = '''
        SELECT      *
        FROM        cmf_users_online_log
        WHERE       uid=%(uid)s AND
                    end_time>=%(begin_time)s AND
                    begin_time<=%(end_time)s
        ORDER BY    id ASC;
    '''
    params = {
        'uid': str(uid),
        'begin_time': begin_time,
        'end_time': end_time,
    }
    data = db.query_data_from_db(sql, params)
    return data if data else []


def get_broadcaster_online_seconds(uid, begin_time, end_time):
    """ 获取主播的在线时长 """
    settled_seconds = get_broadcaster_settled_online_seconds(uid, begin_time, end_time)
    unsettled_seconds = get_broadcaster_unsettled_online_seconds(uid, begin_time, end_time)
    return settled_seconds + unsettled_seconds


def get_broadcaster_unsettled_online_seconds(uid, begin_time, end_time):
    """ 获取主播未结算的在线时长 """
    begin_ts = util.strtotime(begin_time)
    end_ts = util.strtotime(end_time)

    user = get_user_detail(uid)
    if user is None:
        return 0

    if user['is_busy'] != 0:
        return 0

    seconds = 0
    set_busy_ts = util.datetimetotime(user['set_busy_time'])
    if begin_ts < set_busy_ts < end_ts:
        seconds = min(24*3600, int(time.time() - set_busy_ts))
    return seconds


def get_broadcaster_settled_online_seconds(uid, begin_time, end_time):
    """ 获取主播结算的在线时长 """
    sql = '''
        SELECT      *
        FROM        cmf_users_online_log
        WHERE       uid=%(uid)s AND
                    end_time>=%(begin_time)s AND
                    begin_time<=%(end_time)s;
    '''
    params = {
        'uid': str(uid),
        'begin_time': begin_time,
        'end_time': end_time,
    }
    data = db.query_data_from_db(sql, params)
    if data is None:
        return 0

    begin_time = util.strtotime(begin_time)
    end_time = util.strtotime(end_time)
    total_seconds = 0
    for record in data:
        record_begin_time = util.datetimetotime(record['begin_time'])
        record_end_time = util.datetimetotime(record['end_time'])
        total_seconds += record['online_seconds']

        if record_begin_time < begin_time:
            total_seconds -= begin_time - record_begin_time
        if record_end_time > end_time:
            total_seconds -= record_end_time - end_time
    return total_seconds


def get_broadcaster_today_call_seconds(uid):
    """ 获取主播的今日的通话时长 """
    begin_time = util.datetimetostr(datetime.date.today())
    end_time = util.datetimetostr(datetime.date.today() + datetime.timedelta(days=1))
    return get_broadcaster_call_seconds(uid, begin_time, end_time)


def get_broadcaster_call_records(uid, begin_time, end_time):
    """ 获取主播的通话记录 """
    sql = '''
        SELECT      c.*,
                    u.avatar AS caller_avatar,
                    u2.avatar AS callee_avatar
        FROM        cmf_vchat_call_log AS c
        JOIN        cmf_users AS u ON u.id=c.caller_uid
        JOIN        cmf_users AS u2 ON u2.id=c.callee_uid
        WHERE       (caller_uid=%(uid)s OR callee_uid=%(uid)s) AND
                    end_time>=%(begin_time)s AND
                    begin_time<=%(end_time)s
        ORDER BY    id DESC;
    '''
    params = {
        'uid': str(uid),
        'begin_time': begin_time,
        'end_time': end_time,
    }
    data = db.query_data_from_db(sql, params)
    if not data:
        return []

    for row in data:
        caller_uid = row['caller_uid']
        callee_uid = row['callee_uid']
        terminator_uid = row['terminator_uid']
        row['target_uid'] = caller_uid if int(uid) == callee_uid else callee_uid
        row['terminator_is_caster'] = True if terminator_uid > 0 and is_broadcaster(terminator_uid) else False
    return data


def get_broadcaster_dropped_call_records(uid, count):
    """ 获取主播的漏接电话记录 """
    sql = f'''
        SELECT      c.*,
                    u.avatar AS caller_avatar,
                    u2.avatar AS callee_avatar
        FROM        cmf_vchat_call_log AS c
        JOIN        cmf_users AS u ON u.id=c.caller_uid
        JOIN        cmf_users AS u2 ON u2.id=c.callee_uid
        WHERE       callee_uid={uid} AND
                    c.status={CALL_STATUS_CANCEL} AND
                    c.chat_seconds>=15
        ORDER BY    id DESC
        LIMIT       {count};
    '''
    data = db.query_data_from_db(sql)
    if not data:
        return []

    for row in data:
        caller_uid = row['caller_uid']
        callee_uid = row['callee_uid']
        row['target_uid'] = caller_uid if int(uid) == callee_uid else callee_uid
    return data


def get_broadcaster_dropped_call_count(uid):
    """ 获取主播的漏接电话记录 """
    sql = f'''
        SELECT      COUNT(*) AS total_count
        FROM        cmf_vchat_call_log AS c
        WHERE       callee_uid={uid} AND
                    c.status={CALL_STATUS_CANCEL} AND
                    c.chat_seconds>=15;
    '''
    data = db.query_data_from_db(sql)
    return data[0]['total_count'] if data else 0


def get_call_records_by_time(begin_time, end_time, request=None):
    """ 获取主播的通话时长 """
    apps_condition = gma_helpers.build_app_filter_condition_for_request(request, 'u2.origin_bundle_id')
    sql = f'''
        SELECT      c.*,
                    u1.avatar AS caller_avatar,
                    u2.avatar AS callee_avatar
        FROM        cmf_vchat_call_log AS c
        JOIN        cmf_users AS u1 ON u1.id=c.caller_uid
        JOIN        cmf_users AS u2 ON u2.id=c.callee_uid
        WHERE       {apps_condition} AND
                    c.end_time>=%(begin_time)s AND
                    c.begin_time<=%(end_time)s
        ORDER BY    id DESC;
    '''
    params = {
        'begin_time': begin_time,
        'end_time': end_time,
    }
    data = db.query_data_from_db(sql, params)
    if not data:
        return []

    for row in data:
        # caller_uid = row['caller_uid']
        # callee_uid = row['callee_uid']
        terminator_uid = row['terminator_uid']
        # row['target_uid'] = caller_uid if int(uid) == callee_uid else callee_uid
        row['terminator_is_caster'] = True if terminator_uid > 0 and is_broadcaster(terminator_uid) else False
    return data


def get_broadcaster_call_seconds(uid, begin_time, end_time):
    """ 获取主播的通话时长 """
    sql = '''
        SELECT      *
        FROM        cmf_vchat_call_log
        WHERE       (caller_uid=%(uid)s OR callee_uid=%(uid)s) AND
                    end_time>=%(begin_time)s AND
                    begin_time<=%(end_time)s;
    '''
    params = {
        'uid': str(uid),
        'begin_time': begin_time,
        'end_time': end_time,
    }
    data = db.query_data_from_db(sql, params)
    if data is None:
        return 0

    begin_time = util.strtotime(begin_time)
    end_time = util.strtotime(end_time)
    total_seconds = 0
    for record in data:
        record_begin_time = util.datetimetotime(record['begin_time'])
        record_end_time = util.datetimetotime(record['end_time'])
        total_seconds += record['chat_seconds']

        if record_begin_time < begin_time:
            total_seconds -= begin_time - record_begin_time
        if record_end_time > end_time:
            total_seconds -= record_end_time - end_time
    return total_seconds


def get_broadcaster_income(uid, begin_time, end_time):
    """ 获取主播的收益 """
    sql = '''
        SELECT      IFNULL(SUM(amount), 0) AS amount
        FROM        cmf_users_consumption_log
        WHERE       uid=%(uid)s AND
                    add_time>=%(begin_time)s AND
                    add_time<=%(end_time)s AND
                    ((type>1000 AND type<1100) OR (type>2000 AND type<2100) OR (type=1110));
    '''
    # amount > 0 AND
    params = {
        'uid': str(uid),
        'begin_time': begin_time,
        'end_time': end_time,
    }
    data = db.query_data_from_db(sql, params)
    return data[0]['amount'] if data else 0


def get_broadcaster_total_income(uid):
    """ 获取主播的累计收益 """
    sql = '''
        SELECT      IFNULL(SUM(amount), 0) AS amount
        FROM        cmf_users_consumption_log
        WHERE       uid=%(uid)s AND
                    amount > 0 AND
                    ((type>1000 AND type<1100) OR (type>2000 AND type<2100));
    '''
    params = {
        'uid': str(uid),
    }
    data = db.query_data_from_db(sql, params)
    return data[0]['amount'] if data else 0


def get_user_diamond_input_records(uid, count):
    """ 获取用户的钻石收入记录 """
    sql = f'''
        SELECT      *
        FROM        cmf_users_consumption_log AS c
        LEFT JOIN   cmf_users AS u ON c.target_uid=u.id
        WHERE       c.uid={uid} AND
                    c.amount > 0 AND
                    c.type<200
        ORDER BY    c.id DESC
        LIMIT       {count};
    '''
    data = db.query_data_from_db(sql)
    return data if data else []


def get_user_diamond_output_records(uid, count):
    """ 获取用户的钻石消耗记录 """
    sql = f'''
        SELECT      *
        FROM        cmf_users_consumption_log AS c
        JOIN        cmf_users AS u ON c.target_uid=u.id
        WHERE       c.uid={uid} AND
                    c.amount < 0 AND
                    c.type<200
        ORDER BY    c.id DESC
        LIMIT       {count};
    '''
    data = db.query_data_from_db(sql)
    return data if data else []


def get_user_ticket_input_records(uid, begin_time, end_time):
    """ 获取用户的趣票收入记录 """
    sql = '''
        SELECT      *
        FROM        cmf_users_consumption_log AS c
        LEFT JOIN   cmf_users AS u ON c.target_uid=u.id
        WHERE       c.uid=%(uid)s AND
                    c.add_time>=%(begin_time)s AND
                    c.add_time<=%(end_time)s AND
                    ((c.type>1000 AND c.type<1100) OR (c.type>2000 AND c.type<2100) OR (c.type=1110))
        ORDER BY    c.id DESC;
    '''
    # c.amount > 0 AND
    params = {
        'uid': str(uid),
        'begin_time': begin_time,
        'end_time': end_time,
    }
    data = db.query_data_from_db(sql, params)
    return data if data else []


def get_user_total_diamond_input_amount(uid):
    """ 获取用户的累计钻石消耗数 """
    sql = '''
        SELECT      IFNULL(SUM(amount), 0) AS amount
        FROM        cmf_users_consumption_log
        WHERE       uid=%(uid)s AND
                    amount > 0 AND
                    type<1000;
    '''
    params = {
        'uid': str(uid),
    }
    data = db.query_data_from_db(sql, params)
    return data[0]['amount'] if data else 0


def get_user_total_diamond_output_amount(uid):
    """ 获取用户的累计钻石消耗数 """
    sql = '''
        SELECT      IFNULL(SUM(amount), 0) AS amount
        FROM        cmf_users_consumption_log
        WHERE       uid=%(uid)s AND
                    amount < 0 AND
                    type<1000;
    '''
    params = {
        'uid': str(uid),
    }
    data = db.query_data_from_db(sql, params)
    return data[0]['amount'] if data else 0


def get_total_user_count_of_channel(channel, begin_time=''):
    """ 获取指定渠道的总用户数量 """
    # begin_time = ''
    end_time = util.get_tomorrow_str()
    return get_new_user_count_in_channel(channel, begin_time, end_time)


def get_new_user_count_in_channel(channel, begin_time, end_time):
    """ 获取指定渠道指定时间段的新增用户数量 """
    sql = '''
        SELECT      COUNT(*) AS user_count
        FROM        cmf_users AS u
        WHERE       u.origin_channel=%(channel)s AND
                    u.create_time>=%(begin_time)s AND
                    u.create_time<=%(end_time)s AND
                    u.parent_partner_id=0;
    '''
    params = {
        'channel': channel,
        'begin_time': begin_time,
        'end_time': end_time,
    }
    data = db.query_data_from_db(sql, params)
    if data is None:
        return 0
    return data[0]['user_count']


def get_user_list_in_channel(channel, begin_time, end_time):
    """ 获取指定渠道指定时间段的新增用户列表 """
    sql = '''
        SELECT      *
        FROM        cmf_users AS u
        WHERE       u.origin_channel=%(channel)s AND
                    u.create_time>=%(begin_time)s AND
                    u.create_time<=%(end_time)s AND
                    u.parent_partner_id=0
        LIMIT       1000;
    '''
    params = {
        'channel': channel,
        'begin_time': begin_time,
        'end_time': end_time,
    }
    data = db.query_data_from_db(sql, params)
    return data if data else []


def get_total_pay_money_of_channel(channel, begin_time=''):
    """ 获取指定渠道的累计充值金额 """
    # begin_time = ''
    end_time = util.get_tomorrow_str()
    return get_pay_amount_in_channel(channel, begin_time, end_time)


def get_pay_amount_in_channel(channel, begin_time, end_time):
    """ 获取指定渠道指定时间段的充值数量 """
    params = {
        'channel': channel,
        'begin_time': begin_time,
        'end_time': end_time,
    }

    # 计算直接推广的用户充值金额
    sql = '''
        SELECT      IFNULL(SUM(money), 0) AS money1
        FROM        cmf_users_charge AS c
        JOIN        cmf_users AS u ON c.uid=u.id
        WHERE       c.channel=%(channel)s AND
                    c.add_time>=%(begin_time)s AND
                    c.add_time<=%(end_time)s AND
                    c.status=1 AND
                    u.parent_agent_id=0 AND
                    u.parent_partner_id=0 AND
                    u.is_test_account=0;
    '''
    data = db.query_data_from_db(sql, params)
    if not data:
        return 0
    money1 = data[0]['money1']
    return money1


def get_pay_list_in_channel(channel, begin_time, end_time):
    """ 获取指定渠道指定时间段的充值列表 """
    sql = '''
        SELECT      *
        FROM        cmf_users_charge AS c
        JOIN        cmf_users AS u ON c.uid=u.id
        WHERE       c.channel=%(channel)s AND
                    c.add_time>=%(begin_time)s AND
                    c.add_time<=%(end_time)s AND
                    c.status=1  AND
                    u.parent_partner_id=0 AND
                    u.is_test_account=0
        LIMIT       1000;
    '''
    params = {
        'channel': channel,
        'begin_time': begin_time,
        'end_time': end_time,
    }
    data = db.query_data_from_db(sql, params)
    return data if data else []


def is_banned_user(uid):
    """ 判断是否是冻结的用户 """
    sql = f'''
        SELECT      id
        FROM        cmf_users_ban
        WHERE       uid={uid}
        LIMIT       1;
    '''
    data = db.query_data_from_db(sql)
    return False if data is None or len(data) == 0 else True


def is_banned_device(device_id):
    """ 判断是否是冻结的设备 """
    sql = f'''
        SELECT      id
        FROM        cmf_users_ban_device
        WHERE       device_id="{device_id}"
        LIMIT       1;
    '''
    data = db.query_data_from_db(sql)
    return False if data is None or len(data) == 0 else True


def is_banned_ip(ip):
    """ 判断是否是冻结的IP """
    sql = f'''
        SELECT      id
        FROM        cmf_users_ban_ip
        WHERE       ip="{ip}"
        LIMIT       1;
    '''
    data = db.query_data_from_db(sql)
    return False if data is None or len(data) == 0 else True


def ban_user(uid, gm='', comment=''):
    """ 冻结指定的用户 """
    sql = '''
        INSERT INTO cmf_users_ban
        SET         uid=%(uid)s,
                    gm=%(gm)s,
                    comment=%(comment)s;
    '''
    params = {
        'uid': str(uid),
        'gm': gm,
        'comment': comment,
    }
    affected_rows = db.execute_sql(sql, params)
    return affected_rows == 1


def ban_user_device(uid, device_id, gm='', comment=''):
    """ 冻结指定的设备 """
    sql = '''
        INSERT INTO cmf_users_ban_device
        SET         uid=%(uid)s,
                    device_id=%(device_id)s,
                    gm=%(gm)s,
                    comment=%(comment)s;
    '''
    params = {
        'uid': str(uid),
        'device_id': device_id,
        'gm': gm,
        'comment': comment,
    }
    affected_rows = db.execute_sql(sql, params)
    return affected_rows == 1


def ban_user_ip(uid, ip, gm='', comment=''):
    """ 冻结指定的设备 """
    sql = '''
        INSERT INTO cmf_users_ban_ip
        SET         uid=%(uid)s,
                    ip=%(ip)s,
                    gm=%(gm)s,
                    comment=%(comment)s;
    '''
    params = {
        'uid': str(uid),
        'ip': ip,
        'gm': gm,
        'comment': comment,
    }
    affected_rows = db.execute_sql(sql, params)
    return affected_rows == 1


def unban_user(uid):
    """ 撤销冻结指定的用户 """
    sql = '''
        DELETE FROM cmf_users_ban
        WHERE       uid=%(uid)s;
    '''
    params = {
        'uid': str(uid),
    }
    affected_rows = db.execute_sql(sql, params)
    return affected_rows == 1


def unban_user_device(device_id):
    """ 撤销冻结指定的设备 """
    sql = '''
        DELETE FROM cmf_users_ban_device
        WHERE       device_id=%(device_id)s;
    '''
    params = {
        'device_id': device_id,
    }
    affected_rows = db.execute_sql(sql, params)
    return affected_rows == 1


def unban_user_ip(ip):
    """ 撤销冻结指定的IP """
    sql = '''
        DELETE FROM cmf_users_ban_ip
        WHERE       ip=%(ip)s;
    '''
    params = {
        'ip': ip,
    }
    affected_rows = db.execute_sql(sql, params)
    return affected_rows == 1


def get_banned_user_count(request=None):
    """ 获取被封禁的账号的数量 """
    apps_condition = gma_helpers.build_app_filter_condition_for_request(request)
    sql = f'''
        SELECT      COUNT(*) AS user_count
        FROM        cmf_users_ban AS b
        JOIN        cmf_users AS u ON b.uid=u.id
        WHERE       {apps_condition};
    '''
    data = db.query_data_from_db(sql)
    if data is None:
        return 0
    return data[0]['user_count']


def get_banned_user_list(request=None):
    """ 获取被封禁的用户列表 """
    apps_condition = gma_helpers.build_app_filter_condition_for_request(request)
    sql = f'''
        SELECT      *
        FROM        cmf_users_ban AS b
        JOIN        cmf_users AS u ON b.uid=u.id
        JOIN        cmf_users_auth AS a ON a.uid=b.uid
        WHERE       {apps_condition}
        ORDER BY    b.id DESC;
    '''
    data = db.query_data_from_db(sql)
    if data is None:
        return []

    for record in data:
        record['avatar'] = util.cdn_url(record['avatar'])
        record['star_desp'] = ''
        record['is_ios_device'] = 'iphone' in record['last_login_phone'].lower()

    return data


# def get_watcher_list(count):
#     """ 获取观众列表 """
#     sql = f'''
#         SELECT      *
#         FROM        cmf_users AS u
#         JOIN        cmf_users_auth AS a ON a.uid=u.id
#         WHERE       a.status=NULL
#         ORDER BY    b.id DESC
#         LIMIT       {count};
#     '''
#     data = db.query_data_from_db(sql)
#     return [] if data is None else data

def get_paid_new_user_count(begin_time, end_time, channel=None, team=None):
    """ 获取指定日期的新增付费用户数量 """
    where = [
        f'u.create_time>="{begin_time}"',
        f'u.create_time<="{end_time}"',
        'c.status=1'
    ]
    if channel:
        where.append(f'u.origin_channel="{channel}"')
    if team == TEAM_A:
        where.append('(u.id % 2) = 1')
    elif team == TEAM_B:
        where.append('(u.id % 2) != 1')
    elif team == TEAM_C:
        where.append('((u.id % 10 = 0) OR (u.id % 10 = 2))')
    elif team == TEAM_D:
        where.append('((u.id % 10 = 4) OR (u.id % 10 = 6))')
    condition = ' AND '.join(where)

    sql = f'''
        SELECT      IFNULL(COUNT(DISTINCT c.uid), 0) AS total_count
        FROM        cmf_users AS u
        JOIN        cmf_users_charge AS c ON c.uid=u.id
        WHERE       {condition};
    '''
    data = db.query_data_from_db(sql)
    return data[0]['total_count'] if data else 0


def get_repaid_new_user_count(begin_time, end_time, channel=None, team=None):
    """ 获取指定日期的新增付费用户数量 """
    where = [
        f'u.create_time>="{begin_time}"',
        f'u.create_time<="{end_time}"',
        'c.status=1'
    ]
    if channel:
        where.append(f'u.origin_channel="{channel}"')
    if team == TEAM_A:
        where.append('(u.id % 2) = 1')
    elif team == TEAM_B:
        where.append('(u.id % 2) != 1')
    elif team == TEAM_C:
        where.append('((u.id % 10 = 0) OR (u.id % 10 = 2))')
    elif team == TEAM_D:
        where.append('((u.id % 10 = 4) OR (u.id % 10 = 6))')
    condition = ' AND '.join(where)

    sql = f'''
        SELECT      count(*) AS repaid_user_count
        FROM        (SELECT      c.uid, IFNULL(COUNT(*), 0) AS repaid_count
                     FROM        cmf_users_charge AS c
                     JOIN        cmf_users AS u ON c.uid=u.id
                     WHERE       {condition}
                     group by    c.uid) as t
        WHERE        t.repaid_count>1;
    '''
    data = db.query_data_from_db(sql)
    return data[0]['repaid_user_count'] if data else 0


def get_new_user_count(begin_time, end_time, channel=None, team=None, request=None):
    """ 获取指定日期的新增用户数量 """
    apps_condition = gma_helpers.build_app_filter_condition_for_request(request)

    ext = ' true '
    if team == TEAM_A:
        ext = 'u.id %% 2 = 1 '
    elif team == TEAM_B:
        ext = 'u.id %% 2 != 1 '
    elif team == TEAM_C:
        ext = '((u.id %% 10 = 0) OR (u.id %% 10 = 2)) '
    elif team == TEAM_D:
        ext = '((u.id %% 10 = 4) OR (u.id %% 10 = 6)) '
    else:
        pass

    if channel is None:
        sql = f'''
            SELECT      IFNULL(COUNT(*), 0) AS total_count
            FROM        cmf_users AS u
            WHERE       {apps_condition} AND
                        {ext} AND
                        u.create_time>=%(begin_time)s AND
                        u.create_time<=%(end_time)s;
        '''
    else:
        sql = f'''
            SELECT      IFNULL(COUNT(*), 0) AS total_count
            FROM        cmf_users AS u
            WHERE       {apps_condition} AND
                        {ext} AND
                        u.origin_channel=%(channel)s AND
                        u.create_time>=%(begin_time)s AND
                        u.create_time<=%(end_time)s;
        '''
    params = {
        'begin_time': begin_time,
        'end_time': end_time,
        'channel': channel,
    }
    data = db.query_data_from_db(sql, params)
    return data[0]['total_count'] if data else 0


def get_new_user_count_of_channel_ex(channel, begin_time, end_time):
    """ 获取指定日期的新增用户数量 """
    sql = '''
        SELECT      COUNT(*) AS total_count
        FROM        cmf_users AS u
        WHERE       u.create_time>=%(begin_time)s AND
                    u.create_time<=%(end_time)s AND
                    u.origin_channel=%(channel)s;
    '''
    params = {
        'begin_time': begin_time,
        'end_time': end_time,
        'channel': channel,
    }
    data = db.query_data_from_db(sql, params)
    return data[0]['total_count'] if data else 0


def get_new_caster_count(begin_time, end_time, channel=None, team=None):
    """ 获取指定日期的新增主播数量 """
    ext = ''
    if team == TEAM_A:
        ext = 'u.id %% 2 = 1 AND '
    elif team == TEAM_B:
        ext = 'u.id %% 2 != 1 AND '
    elif team == TEAM_C:
        ext = '((u.id %% 10 = 0) OR (u.id %% 10 = 2)) AND '
    elif team == TEAM_D:
        ext = '((u.id %% 10 = 4) OR (u.id %% 10 = 6)) AND '
    else:
        pass

    if channel is None:
        sql = f'''
            SELECT      IFNULL(COUNT(DISTINCT u.id), 0) AS total_count
            FROM        cmf_users_auth AS a
            JOIN        cmf_users AS u ON u.id=a.uid
            WHERE       {ext}
                        a.create_time>=%(begin_time)s AND
                        a.create_time<=%(end_time)s;
        '''
    else:
        sql = f'''
            SELECT      IFNULL(COUNT(DISTINCT u.id), 0) AS total_count
            FROM        cmf_users_auth AS a
            JOIN        cmf_users AS u ON u.id=a.uid
            WHERE       {ext}
                        u.origin_channel=%(channel)s AND
                        a.create_time>=%(begin_time)s AND
                        a.create_time<=%(end_time)s;
        '''
    params = {
        'begin_time': begin_time,
        'end_time': end_time,
        'channel': channel,
        # 'channel_condition': f'u.origin_channel="{channel}" ' if channel else 'true ',
    }

    data = db.query_data_from_db(sql, params)
    return data[0]['total_count'] if data else 0


def get_ios_new_user_count(begin_time, end_time):
    """ 获取指定日期的iOS新增用户数量 """
    sql = '''
        SELECT      IFNULL(COUNT(*), 0) AS total_count
        FROM        cmf_users AS u
        WHERE       u.origin_channel LIKE "iOS%%" AND
                    u.create_time>=%(begin_time)s AND
                    u.create_time<=%(end_time)s;
    '''
    params = {
        'begin_time': begin_time,
        'end_time': end_time,
    }
    data = db.query_data_from_db(sql, params)
    return data[0]['total_count'] if data else 0


def get_android_new_user_count(begin_time, end_time):
    """ 获取指定日期的Android新增用户数量 """
    sql = '''
        SELECT      IFNULL(COUNT(*), 0) AS total_count
        FROM        cmf_users AS u
        WHERE       u.origin_channel NOT LIKE "iOS%%" AND
                    u.create_time>=%(begin_time)s AND
                    u.create_time<=%(end_time)s;
    '''
    params = {
        'begin_time': begin_time,
        'end_time': end_time,
    }
    data = db.query_data_from_db(sql, params)
    return data[0]['total_count'] if data else 0


def get_new_user_count_by_channel(begin_time, end_time, channel):
    """ 获取指定日期指定渠道的新增用户数量 """
    sql = '''
        SELECT      IFNULL(COUNT(*), 0) AS total_count
        FROM        cmf_users AS u
        WHERE       u.origin_channel=%(channel)s AND
                    u.create_time>=%(begin_time)s AND
                    u.create_time<=%(end_time)s;
    '''
    params = {
        'begin_time': begin_time,
        'end_time': end_time,
        'channel': channel,
    }
    data = db.query_data_from_db(sql, params)
    return data[0]['total_count'] if data else 0


def get_new_user_pay_amount(begin_time, end_time, channel=None, team=None, money=None):
    """ 获取指定时间段内新用户的充值总金额 """
    ext = ''
    if team == TEAM_A:
        ext = 'u.id %% 2 = 1 AND '
    elif team == TEAM_B:
        ext = 'u.id %% 2 != 1 AND '
    elif team == TEAM_C:
        ext = '((u.id %% 10 = 0) OR (u.id %% 10 = 2)) AND '
    elif team == TEAM_D:
        ext = '((u.id %% 10 = 4) OR (u.id %% 10 = 6)) AND '
    else:
        pass

    if money is not None:
        ext += f' c.money={money} AND'

    if channel is None:
        sql = f'''
            SELECT      IFNULL(SUM(money), 0) AS money
            FROM        cmf_users_charge AS c
            JOIN        cmf_users AS u ON c.uid=u.id
            WHERE       {ext}
                        c.add_time>=%(begin_time)s AND
                        c.add_time<=%(end_time)s AND
                        u.create_time>=%(begin_time)s AND
                        u.create_time<=%(end_time)s AND
                        c.status=1 AND
                        u.is_test_account=0;
        '''
    else:
        sql = f'''
            SELECT      IFNULL(SUM(money), 0) AS money
            FROM        cmf_users_charge AS c
            JOIN        cmf_users AS u ON c.uid=u.id
            WHERE       {ext}
                        u.origin_channel=%(channel)s AND
                        c.add_time>=%(begin_time)s AND
                        c.add_time<=%(end_time)s AND
                        u.create_time>=%(begin_time)s AND
                        u.create_time<=%(end_time)s AND
                        c.status=1 AND
                        u.is_test_account=0;
        '''
    params = {
        'begin_time': begin_time,
        'end_time': end_time,
        'channel': channel,
        # 'channel_condition': f'u.origin_channel="{channel}" ' if channel else 'true ',
    }
    data = db.query_data_from_db(sql, params)
    return data[0]['money'] if data else 0


def get_new_user_pay_amount_ex(begin_time, end_time, ext):
    """ 获取指定时间段内新用户的充值总金额 """
    sql = f'''
        SELECT      IFNULL(SUM(money), 0) AS money
        FROM        cmf_users_charge AS c
        JOIN        cmf_users AS u ON c.uid=u.id
        WHERE       {ext}
                    c.add_time>=%(begin_time)s AND
                    c.add_time<=%(end_time)s AND
                    u.create_time>=%(begin_time)s AND
                    u.create_time<=%(end_time)s AND
                    c.status=1;
    '''
    params = {
        'begin_time': begin_time,
        'end_time': end_time,
    }
    data = db.query_data_from_db(sql, params)
    return data[0]['money'] if data else 0


def get_pay_amount(begin_time, end_time, request=None):
    """ 获取指定时间段的充值总金额 """
    apps_condition = gma_helpers.build_app_filter_condition_for_request(request)
    sql = f'''
        SELECT      IFNULL(SUM(money), 0) AS money
        FROM        cmf_users_charge AS c
        JOIN        cmf_users AS u ON c.uid=u.id
        WHERE       {apps_condition} AND
                    c.add_time>=%(begin_time)s AND
                    c.add_time<=%(end_time)s AND
                    c.status=1 AND
                    u.is_test_account=0 AND
                    c.comment!='苹果测试账号';
    '''
    params = {
        'begin_time': begin_time,
        'end_time': end_time,
    }
    data = db.query_data_from_db(sql, params)
    return data[0]['money'] if data else 0


def get_consume_amount(begin_time, end_time, request=None):
    """ 获取指定时间范围内的消耗总量 """
    apps_condition = gma_helpers.build_app_filter_condition_for_request(request)
    sql = f'''
        SELECT      IFNULL(SUM(amount), 0) AS amount
        FROM        cmf_users_consumption_log AS c
        JOIN        cmf_users AS u ON u.id=c.uid
        WHERE       {apps_condition} AND
                    c.amount < 0 AND
                    c.add_time>=%(begin_time)s AND
                    c.add_time<=%(end_time)s AND
                    c.type>100 AND c.type<200;
    '''
    params = {
        'begin_time': begin_time,
        'end_time': end_time,
    }
    data = db.query_data_from_db(sql, params)
    return data[0]['amount'] if data else 0


def get_devkeep_consume_amount(begin_time, end_time):
    """ 获取指定时间范围内邸客用户的消耗总量 """
    sql = '''
        SELECT      IFNULL(SUM(amount), 0) AS amount
        FROM        cmf_users_consumption_log
        WHERE       amount < 0 AND
                    add_time>=%(begin_time)s AND
                    add_time<=%(end_time)s AND
                    type>200 AND type<300;
    '''
    params = {
        'begin_time': begin_time,
        'end_time': end_time,
    }
    data = db.query_data_from_db(sql, params)
    return data[0]['amount'] if data else 0


def get_diamond_consume_list_by_time(begin_time, end_time, request=None):
    """ 获取指定时间范围内的钻石消耗总量 """
    apps_condition = gma_helpers.build_app_filter_condition_for_request(request)
    sql = f'''
        SELECT      c.*,
                    u.avatar AS from_avatar,
                    u2.avatar AS to_avatar
        FROM        cmf_users_consumption_log AS c
        JOIN        cmf_users AS u on u.id=c.uid
        JOIN        cmf_users AS u2 on u2.id=c.target_uid
        WHERE       {apps_condition} AND
                    amount < 0 AND
                    c.add_time>=%(begin_time)s AND
                    c.add_time<=%(end_time)s AND
                    c.type>100 AND c.type<200
        ORDER BY    c.id DESC;
    '''
    params = {
        'begin_time': begin_time,
        'end_time': end_time,
    }
    data = db.query_data_from_db(sql, params)
    return data if data else []


def get_devkeep_diamond_consume_list_by_time(begin_time, end_time):
    """ 获取devkeep指定时间范围内的钻石消耗总量 """
    sql = '''
        SELECT      c.*,
                    u.avatar AS from_avatar,
                    u2.avatar AS to_avatar
        FROM        cmf_users_consumption_log AS c
        JOIN        cmf_users AS u on u.id=c.uid
        JOIN        cmf_users AS u2 on u2.id=c.target_uid
        WHERE       amount < 0 AND
                    c.add_time>=%(begin_time)s AND
                    c.add_time<=%(end_time)s AND
                    c.type>200 AND c.type<300
        ORDER BY    c.id DESC;
    '''
    params = {
        'begin_time': begin_time,
        'end_time': end_time,
    }
    data = db.query_data_from_db(sql, params)
    return data if data else []


def get_charged_diamond(begin_time, end_time):
    """ 获取指定时间段的充值的钻石数 """
    sql = '''
        SELECT      IFNULL(SUM(coin), 0) AS amount
        FROM        cmf_users_charge AS c
        JOIN        cmf_users AS u ON c.uid=u.id
        WHERE       c.add_time>=%(begin_time)s AND
                    c.add_time<=%(end_time)s AND
                    c.status=1 AND
                    u.is_test_account=0;
    '''
    params = {
        'begin_time': begin_time,
        'end_time': end_time,
    }
    data = db.query_data_from_db(sql, params)
    return data[0]['amount'] if data else 0


def get_test_charged_diamond(begin_time, end_time):
    """ 获取指定时间段内测试人员的充值钻石数 """
    sql = '''
        SELECT      IFNULL(SUM(coin), 0) AS amount
        FROM        cmf_users_charge AS c
        JOIN        cmf_users AS u ON c.uid=u.id
        WHERE       c.add_time>=%(begin_time)s AND
                    c.add_time<=%(end_time)s AND
                    c.status=1 AND
                    u.is_test_account=1;
    '''
    params = {
        'begin_time': begin_time,
        'end_time': end_time,
    }
    data = db.query_data_from_db(sql, params)
    return data[0]['amount'] if data else 0


def get_user_consumption_amount(begin_time, end_time, consumption_type):
    """ 获取用户消费金额 """
    sql = '''
        SELECT      IFNULL(SUM(amount), 0) AS amount
        FROM        cmf_users_consumption_log AS c
        JOIN        cmf_users AS u ON c.uid=u.id
        WHERE       c.add_time>=%(begin_time)s AND
                    c.add_time<=%(end_time)s AND
                    c.type=%(type)s;
    '''
    params = {
        'begin_time': begin_time,
        'end_time': end_time,
        'type': str(consumption_type),
    }
    data = db.query_data_from_db(sql, params)
    return data[0]['amount'] if data else 0


def get_user_stat_data():
    """ 获取用户的统计数据 """
    sql = '''
        SELECT      IFNULL(SUM(diamond), 0) AS total_diamond,
                    IFNULL(SUM(ticket), 0) AS total_ticket,
                    IFNULL(SUM(experience), 0) AS total_exp,
                    IFNULL(COUNT(*), 0) AS total_user_count

        FROM        cmf_users;
    '''
    data = db.query_data_from_db(sql)
    return data[0] if data else None


def get_total_follow_count():
    """ 获取用户累计关注数 """
    sql = '''
        SELECT      IFNULL(COUNT(*), 0) AS total_count
        FROM        cmf_users_follow;
    '''
    data = db.query_data_from_db(sql)
    return data[0]['total_count'] if data else 0


def set_user_is_busy(uid, is_busy):
    """ 设置用户的勿扰模式 """
    now = util.now()
    sql = f'''
        UPDATE      cmf_users
        SET         is_busy={is_busy},
                    set_busy_time="{now}"
        WHERE       id={uid}
        LIMIT       1;
    '''
    affected_rows = db.execute_sql(sql)
    return affected_rows == 1


def get_active_liveid(uid):
    """ 获取指定用户当前的通话ID """
    sql = f'''
        SELECT      *
        FROM        cmf_vchat_call_log
        WHERE       (caller_uid={uid} OR callee_uid={uid}) AND
                    status<={CALL_STATUS_CALLING}
        ORDER BY    id DESC
        LIMIT       1;
    '''
    data = db.query_data_from_db(sql)
    return data[0]['id'] if data else 0


def get_channels():
    """ 获取渠道列表 """
    sql = f'''
        SELECT      DISTINCT(channel)
        FROM        cmf_users;
    '''
    data = db.query_data_from_db(sql)
    data = data if data else []
    return [x['channel'] for x in data]


def get_new_user_count_of_channel(channel, day):
    """ 获取指定渠道在指定日期的新增用户数量 """
    where = [
        f'DATE(create_time)="{day}"',
    ]
    if channel:
        where.append(f'channel="{channel}"')
    condition = ' AND '.join(where)

    sql = f'''
        SELECT      COUNT(*) AS user_count
        FROM        cmf_users
        WHERE       {condition};
    '''
    data = db.query_data_from_db(sql)
    return data[0]['user_count'] if data else 0


def get_new_pay_user_count_of_channel(channel, day):
    """ 获取指定渠道在指定日期的充值用户数量 """
    where = [
        f'c.status=1',
        f'DATE(c.add_time)="{day}"',
        f'DATE(u.create_time)="{day}"',
    ]
    if channel:
        where.append(f'u.origin_channel="{channel}"')
    condition = ' AND '.join(where)

    sql = f'''
        SELECT      COUNT(DISTINCT(c.uid)) AS user_count
        FROM        cmf_users_charge AS c
        JOIN        cmf_users AS u ON u.id=c.uid
        WHERE       {condition};
    '''
    data = db.query_data_from_db(sql)
    return data[0]['user_count'] if data else 0


def get_old_pay_user_count_of_channel(channel, day):
    """ 获取指定渠道在指定日期的老的充值用户数量 """
    where = [
        f'c.status=1',
        f'DATE(c.add_time)="{day}"',
        f'DATE(u.create_time)<"{day}"',
    ]
    if channel:
        where.append(f'u.origin_channel="{channel}"')
    condition = ' AND '.join(where)

    sql = f'''
        SELECT      COUNT(DISTINCT(c.uid)) AS user_count
        FROM        cmf_users_charge AS c
        JOIN        cmf_users AS u ON u.id=c.uid
        WHERE       {condition};
    '''
    data = db.query_data_from_db(sql)
    return data[0]['user_count'] if data else 0


def get_repay_user_count_of_channel(channel, day):
    """ 获取指定渠道在指定日期的复充用户数量 """
    where = [
        f'c.status=1',
        f'DATE(u.create_time)="{day}"',
    ]
    if channel:
        where.append(f'u.origin_channel="{channel}"')
    condition = ' AND '.join(where)

    sql = f'''
            SELECT COUNT(*) AS repay_user_count FROM (
                SELECT      c.uid, IFNULL(COUNT(*), 0) AS total_count
                FROM        cmf_users AS u
                JOIN        cmf_users_charge AS c ON c.uid=u.id
                WHERE       {condition}
                GROUP BY    c.uid) t
            WHERE t.total_count>1;
    '''
    data = db.query_data_from_db(sql)
    return data[0]['repay_user_count'] if data else 0


def get_total_pay_amount_of_channel(channel, day):
    """ 获取指定渠道在指定日期的累计充值金额 """
    where = [
        f'c.status=1',
        f'DATE(c.add_time)="{day}"',
    ]
    if channel:
        where.append(f'u.origin_channel="{channel}"')
    condition = ' AND '.join(where)

    sql = f'''
        SELECT      IFNULL(SUM(money), 0) AS total_money
        FROM        cmf_users_charge AS c
        JOIN        cmf_users AS u ON u.id=c.uid
        WHERE       {condition};
    '''
    data = db.query_data_from_db(sql)
    return data[0]['total_money'] if data else 0


def get_new_user_total_pay_amount_of_channel(channel, day):
    """ 获取指定渠道在指定日期的新增用户的充值 """
    where = [
        f'c.status=1',
        f'DATE(c.add_time)="{day}"',
        f'DATE(u.create_time)="{day}"',
    ]
    if channel:
        where.append(f'u.origin_channel="{channel}"')
    condition = ' AND '.join(where)

    sql = f'''
        SELECT      IFNULL(SUM(money), 0) AS total_money
        FROM        cmf_users_charge AS c
        JOIN        cmf_users AS u ON u.id=c.uid
        WHERE       {condition};
    '''
    data = db.query_data_from_db(sql)
    return data[0]['total_money'] if data else 0


def get_old_user_total_pay_amount_of_channel(channel, day):
    """ 获取指定渠道在指定日期的老用户的充值 """
    where = [
        f'c.status=1',
        f'DATE(c.add_time)="{day}"',
        f'DATE(u.create_time)<"{day}"',
    ]
    if channel:
        where.append(f'u.origin_channel="{channel}"')
    condition = ' AND '.join(where)

    sql = f'''
        SELECT      IFNULL(SUM(money), 0) AS total_money
        FROM        cmf_users_charge AS c
        JOIN        cmf_users AS u ON u.id=c.uid
        WHERE       {condition};
    '''
    data = db.query_data_from_db(sql)
    return data[0]['total_money'] if data else 0


def get_new_users_total_online_seconds_of_channel(channel, day):
    """ 获取指定渠道在指定日期的新增用户的累计在线时长 """

    sql = f'''
        SELECT      IFNULL(SUM(UNIX_TIMESTAMP(last_login_time)- UNIX_TIMESTAMP(create_time)), 0) AS total_online_seconds
        FROM        cmf_users
        WHERE       channel='{channel}' AND
                    DATE(create_time)='{day}' AND
                    DATE(last_login_time)='{day}';
    '''
    data = db.query_data_from_db(sql)
    return data[0]['total_online_seconds'] if data else 0


def get_new_consume_user_count_of_channel(channel, day, consume_type):
    """ 获取指定渠道在指定日期的消费用户的数量 """
    sql = f'''
        SELECT      COUNT(DISTINCT(c.uid)) AS user_count
        FROM        cmf_users_consumption_log AS c
        JOIN        cmf_users AS u ON u.id=c.uid
        WHERE       c.type={consume_type} AND
                    u.origin_channel='{channel}' AND
                    DATE(u.create_time)='{day}';
    '''
    data = db.query_data_from_db(sql)
    return data[0]['user_count'] if data else 0


def get_follow_caster_new_user_count(channel, day):
    """ 获取指定渠道在指定日期的关注过主播的用户的数量 """
    sql = f'''
        SELECT      COUNT(DISTINCT(f.uid)) AS user_count
        FROM        cmf_users_follow AS f
        JOIN        cmf_users AS u ON u.id=f.uid
        WHERE       u.origin_channel='{channel}' AND
                    DATE(u.create_time)='{day}';
    '''
    data = db.query_data_from_db(sql)
    return data[0]['user_count'] if data else 0


def get_ban_caster_new_user_count(channel, day):
    """ 获取指定渠道在指定日期的屏蔽过主播的用户的数量 """
    sql = f'''
        SELECT      COUNT(DISTINCT(b.uid)) AS user_count
        FROM        cmf_users_blacklist AS b
        JOIN        cmf_users AS u ON u.id=b.uid
        WHERE       u.origin_channel='{channel}' AND
                    DATE(u.create_time)='{day}';
    '''
    data = db.query_data_from_db(sql)
    return data[0]['user_count'] if data else 0


def get_call_caster_new_user_count(channel, day):
    """ 获取指定渠道在指定日期的呼叫过主播的用户的数量 """
    sql = f'''
        SELECT      COUNT(DISTINCT(c.caller_uid)) AS user_count
        FROM        cmf_vchat_call_log AS c
        JOIN        cmf_users AS u ON u.id=c.caller_uid
        WHERE       u.origin_channel='{channel}' AND
                    DATE(u.create_time)='{day}';
    '''
    data = db.query_data_from_db(sql)
    return data[0]['user_count'] if data else 0


def get_user_count_by_star(star, request=None):
    """ 获取指定星级的主播数量 """
    apps_condition = gma_helpers.build_app_filter_condition_for_request(request)
    sql = f'''
        SELECT      COUNT(*) AS count
        FROM        cmf_users_auth AS a
        JOIN        cmf_users AS u ON u.id=a.uid
        WHERE       {apps_condition} AND
                    a.star={star} AND
                    a.status={AUTH_STATUS_CERTIFIED};
    '''
    data = db.query_data_from_db(sql)
    return data[0]['count'] if data else 0


def get_user_list_by_drop_count(count=100, request=None):
    """ 根据漏接次数的多少获取主播列表 """
    apps_condition = gma_helpers.build_app_filter_condition_for_request(request)
    sql = f'''
        SELECT      *,
                    c.callee_uid AS uid,
                    count(*) AS drop_count,
                    u.nickname,
                    u.avatar,
                    u.last_login_phone
        FROM        cmf_vchat_call_log AS c
        JOIN        cmf_users AS u ON u.id=c.callee_uid
        JOIN        cmf_users_auth AS a ON a.uid=u.id
        WHERE       {apps_condition} AND
                    c.status=3 AND
                    c.chat_seconds>=15 AND
                    u.is_test_account=0
        GROUP BY    c.callee_uid
        ORDER BY    drop_count DESC
        LIMIT       {count};
    '''
    data = db.query_data_from_db(sql)
    if data is None:
        return []

    for record in data:
        record['avatar'] = util.cdn_url(record['avatar'])
        record['star_desp'] = '⭐️' * record['star']
        record['is_ios_device'] = 'iphone' in record['last_login_phone'].lower()
    return data


def get_agent_invited_user_count(parent_agent_id):
    """ 获取指定代理邀请的用户数量 """
    sql = f'''
        SELECT      COUNT(*) AS user_count
        FROM        cmf_users
        WHERE       parent_agent_id={parent_agent_id};
    '''
    data = db.query_data_from_db(sql)
    return data[0]['user_count'] if data else 0


def get_agent_invited_user_list(aid, count=50):
    """ 获取代理邀请的用户列表 """
    sql = f'''
        SELECT      *,
                    u.id AS uid
        FROM        cmf_users AS u
        WHERE       u.parent_agent_id={aid}
        ORDER BY    u.id DESC
        LIMIT       {count};
    '''
    data = db.query_data_from_db(sql)
    if data is None:
        return []

    for record in data:
        record['avatar'] = util.cdn_url(record['avatar'])
        record['is_ios_device'] = 'iphone' in record['last_login_phone'].lower()
        record['star_desp'] = ''

    return data


def get_user_chat_targets(uid, count):
    """ 获取用户的私信聊天对象列表 """
    # 给用户发送过消息的对象列表
    sql = f'''
        SELECT      m1.*, u.nickname, u.avatar
        FROM        cmf_users_message m1
        JOIN        (SELECT uid, touid, MAX(id) id FROM cmf_users_message WHERE touid={uid} GROUP BY uid) AS m2
                        ON m1.id = m2.id AND m1.touid = m2.touid
        JOIN        cmf_users AS u ON u.id=m1.uid
        ORDER BY    is_readed,
                    create_time DESC
        LIMIT       {count};
    '''
    data1 = db.query_data_from_db(sql)
    data1 = data1 if data1 else []

    # 用户发送过消息的对象列表
    sql = f'''
        SELECT      m1.*, u.nickname, u.avatar
        FROM        cmf_users_message m1
        JOIN        (SELECT uid, touid, MAX(id) id FROM cmf_users_message WHERE uid={uid} GROUP BY touid) AS m2
                        ON m1.id = m2.id AND m1.touid = m2.touid
        JOIN        cmf_users AS u ON u.id=m1.touid
        ORDER BY    create_time DESC
        LIMIT       {count};
    '''
    data2 = db.query_data_from_db(sql)
    data2 = data2 if data2 else []

    # 过滤掉重复的用户
    # TODO: 此处保留了发送给自己的最后一条记录，但并不一定是最新的记录
    data = []
    keys = set()
    for user in data1 + data2:
        uids = [user['uid'], user['touid']]
        uids.sort()
        key = '-'.join([str(x) for x in uids])
        if key in keys:
            continue
        else:
            keys.add(key)

        if util.is_image_url(user['message']):
            user['message'] = '[图片]'
        data.append(user)

    data.sort(key=lambda x: (x['is_readed'], -util.datetimetotime(x['create_time'])))
    return data


def get_user_chat_messages(uid, touid, count):
    """ 获取两个用户之间的聊天详情 """
    sql = f'''
        SELECT      *
        FROM        cmf_users_message
        WHERE       (uid={uid} AND touid={touid}) OR (uid={touid} AND touid={uid})
        ORDER BY    id
        LIMIT       {count};
    '''
    data = db.query_data_from_db(sql)
    return data if data else []


def set_user_chat_messages_readed(uid, touid, is_readed):
    """ 修改聊天信息为已读"""
    sql = f'''
        UPDATE      cmf_users_message
        SET         is_readed={is_readed}
        WHERE       uid={uid} AND touid={touid};
    '''
    affected_rows = db.execute_sql(sql)
    return affected_rows == 1


def get_unread_message_count(uid):
    """ 获取客服未读信息的数量"""
    sql = f'''
        SELECT      COUNT(*) AS total_count
        FROM        cmf_users_message
        WHERE       touid={uid} AND
                    is_readed=0;
    '''
    data = db.query_data_from_db(sql)
    return data[0]['total_count'] if data else 0


def save_private_message(uid, touid, message):
    """ 保存私信记录 """
    sql = '''
        INSERT INTO cmf_users_message
        SET         uid=%(uid)s,
                    touid=%(touid)s,
                    message=%(message)s,
                    is_readed=1;
    '''
    params = {
        'uid': str(uid),
        'touid': str(touid),
        'message': message,
    }
    affected_rows = db.execute_sql(sql, params)
    return affected_rows == 1


def is_lifelong_vip(uid):
    """ 判断用户是否是终身vip """
    sql = f'''
        SELECT      COUNT(*) AS buy_count
        FROM        cmf_users_vip
        WHERE       uid='{uid}' AND
                    card_type=4;
    '''
    data = db.query_data_from_db(sql)
    return data[0]['buy_count'] > 0 if data else False


def update_broadcaster_recommend_value_by_uid(uid, recommend_value):
    """ 更新主播的推荐值 """
    now = util.now()
    sql = f'''
        UPDATE      cmf_users_auth
        SET         recommend_value={recommend_value},
                    update_time="{now}"
        WHERE       uid={uid}
        LIMIT       1;
    '''
    affected_rows = db.execute_sql(sql)
    return affected_rows == 1


def update_broadcaster_recommend_value_by_value(old_value, recommend_value):
    """ 更新主播的推荐值 """
    now = util.now()
    sql = f'''
        UPDATE      cmf_users_auth
        SET         recommend_value={recommend_value},
                    update_time="{now}"
        WHERE       recommend_value={old_value}
        LIMIT       1;
    '''
    affected_rows = db.execute_sql(sql)
    return affected_rows == 1


def get_min_recommend_value():
    """ 获取当前最小的推荐值 """
    sql = f'''
        SELECT      IFNULL(MIN(recommend_value), 0) AS last_recommend_value
        FROM        cmf_users_auth
        WHERE       recommend_value>0;
    '''
    data = db.query_data_from_db(sql)
    return data[0]['last_recommend_value'] if data else 0


def get_max_recommend_value():
    """ 获取当前最大的推荐值 """
    sql = f'''
        SELECT      IFNULL(MAX(recommend_value), 0) AS last_recommend_value
        FROM        cmf_users_auth
        WHERE       recommend_value>0;
    '''
    data = db.query_data_from_db(sql)
    return data[0]['last_recommend_value'] if data else 0


def get_recommend_position(uid):
    """ 获取指定推荐值的推荐位 """
    user = get_user_detail(uid)
    if user is None:
        return 0

    recommend_value = user['recommend_value']
    sql = f'''
        SELECT      COUNT(*) AS recommend_position
        FROM        cmf_users_auth
        WHERE       recommend_value>0 AND recommend_value>={recommend_value};
    '''
    data = db.query_data_from_db(sql)
    return data[0]['recommend_position'] if data else 0


def get_user_recommend_desp(uid):
    """ 获取用户的推荐描述 """
    user = get_user_detail(uid)
    if user is None:
        return ''

    recommend_value = user['recommend_value']
    recommend_position = get_recommend_position(uid)
    if recommend_value == 0:
        recommend_desp = '未推荐'
    elif recommend_value > 0:
        recommend_desp = f'{recommend_position}'
    else:
        recommend_desp = f'置底'
    return recommend_desp


def get_new_users_by_channel(begin_time, end_time, app=None):
    """ 获取指定时间内各个渠道的新增用户数 """
    where = [
        f'u.create_time>="{begin_time}"',
        f'u.create_time<="{end_time}"',
    ]
    if app:
        where.append(f'a.app_code="{app}"')
    condition = ' AND '.join(where)

    sql = f'''
        SELECT      channel, IFNULL(COUNT(*), 0) AS value
        FROM        cmf_users AS u
        JOIN        cmf_config_app AS a ON a.bundle_id=u.bundle_id
        WHERE       {condition}
        GROUP BY    channel;
    '''
    data = db.query_data_from_db(sql)
    return data if data else []


def get_new_users_archive_data_in_channel(channel, days):
    """ 获取指定范围内指定渠道的新增用户数据 """
    sql = f'''
        SELECT      date_format(create_time, '%Y-%m-%d') AS user_create_date, COUNT(*) AS user_count
        FROM        cmf_users
        WHERE       origin_channel="{channel}"
        GROUP BY    user_create_date
        ORDER BY    user_create_date DESC
        LIMIT       {days};
    '''
    data = db.query_data_from_db(sql)
    return data if data else []


def get_pay_data_by_channel(begin_time, end_time, app=None):
    """ 获取指定时间内各个渠道的充值数据 """
    where = [
        f'c.add_time>="{begin_time}"',
        f'c.add_time<="{end_time}"',
        'c.status=1',
        'u.is_test_account=0',
    ]
    if app:
        where.append(f'a.app_code="{app}"')
    condition = ' AND '.join(where)

    sql = f'''
        SELECT      c.channel AS channel, IFNULL(SUM(money), 0) AS value
        FROM        cmf_users_charge AS c
        JOIN        cmf_config_app AS a ON a.bundle_id=c.bundle_id
        JOIN        cmf_users AS u ON u.id=c.touid
        WHERE       {condition}
        GROUP BY    channel;
    '''
    data = db.query_data_from_db(sql)
    return data if data else []


def get_pay_archive_data_in_channel(channel, days):
    """ 获取指定范围内指定渠道的充值数据 """
    sql = f'''
        SELECT      date_format(add_time, '%Y-%m-%d') AS pay_date, SUM(money) AS pay_money
        FROM        cmf_users_charge AS c
        JOIN        cmf_users AS u ON u.id=c.touid
        WHERE       c.channel LIKE "{channel}%%" AND c.status=1 AND u.is_test_account=0
        GROUP BY    pay_date
        ORDER BY    pay_date DESC
        LIMIT       {days};
    '''
    data = db.query_data_from_db(sql)
    return data if data else []


def get_channel_create_time(channel):
    """ 获取渠道的创建时间 """
    sql = f'''
        SELECT      *
        FROM        cmf_users AS u
        WHERE       u.origin_channel="{channel}"
        ORDER BY    u.id
        LIMIT       1;
    '''
    data = db.query_data_from_db(sql)
    return data[0]['create_time'] if data else None


def get_daily_new_user_count(day, request=None):
    """ 获取每日的充值统计数据 """
    apps_condition = gma_helpers.build_app_filter_condition_for_request(request)
    sql = f'''
        SELECT      DATE_FORMAT(create_time, '%Y-%m-%d') AS d,
                    COUNT(*) as total_count
        FROM        cmf_users AS u
        WHERE       {apps_condition} AND
                    u.is_test_account=0
        GROUP BY    d
        ORDER BY    d DESC
        LIMIT       {day};
    '''
    data = db.query_data_from_db(sql)
    return data if data else []


def update_allow_free_chat_by_uid(uid, allowed):
    """ 更新主播的推荐值 """
    now = util.now()
    sql = f'''
        UPDATE      cmf_users_auth
        SET         allow_free_chat={allowed}
        WHERE       uid={uid}
        LIMIT       1;
    '''
    affected_rows = db.execute_sql(sql)
    return affected_rows == 1


def get_free_chat_caster_list(count=50, request=None):
    """ 开通免费通话功能的主播列表 """
    apps_condition = gma_helpers.build_app_filter_condition_for_request(request)
    sql = f'''
        SELECT      *
        FROM        cmf_users_auth AS a
        JOIN        cmf_users AS u ON a.uid=u.id
        WHERE       {apps_condition} AND
                    a.status={AUTH_STATUS_CERTIFIED} AND
                    a.allow_free_chat=1
        ORDER BY    u.is_busy,
                    a.recommend_value DESC,
                    a.activity DESC,
                    a.star DESC,
                    a.id DESC
        LIMIT       {count};
    '''

    data = db.query_data_from_db(sql)
    if data is None:
        return []

    for record in data:
        record['avatar'] = util.cdn_url(record['avatar'])
        record['star_desp'] = '⭐️' * record['star']
        record['is_ios_device'] = 'iphone' in record['last_login_phone'].lower()
    return data


def get_allow_free_chat_caster_count(request=None):
    """ 获取允许免费通话的主播数量 """
    apps_condition = gma_helpers.build_app_filter_condition_for_request(request)
    sql = f'''
        SELECT      COUNT(*) AS count
        FROM        cmf_users_auth AS a
        JOIN        cmf_users AS u ON u.id=a.uid
        WHERE       {apps_condition} AND
                    a.status={AUTH_STATUS_CERTIFIED} AND
                    a.allow_free_chat=1;
    '''
    data = db.query_data_from_db(sql)
    return data[0]['count'] if data else 0


def get_new_user_count_by_time(begin_time, end_time, request=None):
    """ 获取指定时间段内的新增用户的数量 """
    apps_condition = gma_helpers.build_app_filter_condition_for_request(request)
    sql = f'''
        SELECT      COUNT(*) AS count
        FROM        cmf_users AS u
        WHERE       {apps_condition} AND
                    u.create_time>=%(begin_time)s AND
                    u.create_time<=%(end_time)s;
    '''
    params = {
        'begin_time': begin_time,
        'end_time': end_time,
    }
    data = db.query_data_from_db(sql, params)
    return data[0]['count'] if data else 0


def get_new_users_by_time(begin_time, end_time, request=None):
    """ 获取指定时间段内的新增用户 """
    apps_condition = gma_helpers.build_app_filter_condition_for_request(request)
    sql = f'''
        SELECT      *,
                    u.id AS uid
        FROM        cmf_users AS u
        WHERE       {apps_condition} AND
                    u.create_time>=%(begin_time)s AND
                    u.create_time<=%(end_time)s
        ORDER BY    id DESC
        LIMIT       5000;
    '''
    params = {
        'begin_time': begin_time,
        'end_time': end_time,
    }
    data = db.query_data_from_db(sql, params)
    return data if data else []


def update_heat_factor(uid, heat_factor):
    """ 更新房间的热度系数 """
    sql = f'''
        UPDATE      cmf_users_auth
        SET         heat_factor={heat_factor}
        WHERE       uid={uid}
        LIMIT       1;
    '''
    affected_rows = db.execute_sql(sql)
    return affected_rows == 1


def update_room_extra_heat(uid, extra_heat):
    """ 更新房间的热度系数 """
    sql = f'''
        UPDATE      cmf_users_auth
        SET         extra_heat={extra_heat}
        WHERE       uid={uid}
        LIMIT       1;
    '''
    affected_rows = db.execute_sql(sql)
    return affected_rows == 1


def update_caster_room_tag(uid, tag_id):
    """" 更新房间品类 """
    sql = f'''
        UPDATE      cmf_users_auth
        SET         room_tag=%(tag_id)s
        WHERE       uid=%(uid)s
        LIMIT       1;
    '''
    params = {
        'uid': str(uid),
        'tag_id': str(tag_id),
    }
    affected_rows = db.execute_sql(sql, params)
    return affected_rows == 1


def recommend_room(uid, allowed):
    """ 推荐/取消推荐房间 """
    now = util.now()
    sql = f'''
        UPDATE      cmf_users_auth
        SET         is_recommended={allowed}
        WHERE       uid={uid}
        LIMIT       1;
    '''
    affected_rows = db.execute_sql(sql)
    return affected_rows == 1


if __name__ == '__main__':
    print('ok')

